A user-defined function, which is a Transact-SQL or common language runtime (CLR) routine that accepts parameters, performs an action, such as a complex calculation, and returns the result of that action as a value. The return value can either be a scalar (single) value or a table.
Stored procedures are similar to procedures in other programming languages in that they can:
- Accept input parameters and return multiple values in the form of output parameters to the calling procedure or batch.
- Contain programming statements that perform operations in the database, including calling other procedures.
- Return a status value to a calling procedure or batch to indicate success or failure (and the reason for failure).
Just simply click to open an object pane for
Function. By using the object pane toolbar below, you can create
new, edit and delete the selected function/procedure.
Create Function/Procedures
To create a new function/procedure
- Select anywhere on the object pane.
- Click the
from the object pane toolbar.
or - Control-click and select New Function from the popup menu.
- Edit function/procedure properties on the appropriate tabs of the Function/Procedure Designer.
Hint: To create new function/procedure you can also control-click the Function node of the navigation pane and select New Function from the popup menu.
To create a new function/procedure with the same properties as one of the existing function/procedure has (using drag and drop method)
Apply to: current schema {same connection}
- Select the function/procedure(s) for copying in the navigation pane/object pane.
- Click and drag the chosen function/procedure(s) to the target location.
- Select one of the following options:
- Copy
- Cancel
- The newly created function/procedure(s) will be named as "function/procedurename_copy".
Apply to: different schema {same connection} different schema {different connection} (Data Transfer tool will be activated)
- Select the function/procedure(s) for copying in the object pane.
- Drag and drop the chosen function/procedure(s) to the target database.
- Select one of the following options:
- Copy
- Cancel
- Control-click the function/procedure in the navigation pane or object pane and choose Duplicate Function.
- The newly created function/procedure will be named as "function/procedurename_copy".
To edit the existing function/procedure
- Select the function/procedure for editing in the navigation pane/object pane.
- Control-click and select the Design Function from the popup menu or simply double-click the function/procedure.
or - Click the
from the object pane toolbar.
- Edit function/procedure properties on the appropriate tabs of the Function/Procedure Designer.
Run Function/Procedure
To run a function/procedure in the navigation pane/object pane
- Select the function/procedure for executing in the navigation pane/object pane.
- Click the Execute from the object pane toolbar.
or - Control-click and select Execute Function from the popup menu.
- View the returned data on the Result tab.
To run a function/procedure in the Function/Procedure Designer
- Create a function/procedure or open the existing function/procedure.
- Click
Execute.
- View the returned data on the Result tab.
Delete Function/Procedure
To delete a function/procedure
- Select the function/procedure for deleting in the navigation pane/object pane.
- Control-click and select the Delete
Function from the popup menu.
or - Click the
from the object pane toolbar.
- Confirm deleting in the dialog window.
Achieve Function/Procedure Information
To achieve a function/procedure information
- Select the function/procedure in the navigation pane/object pane.
- Choose View -> Object Information in the main menu.
or - Click the
from the object pane toolbar.